﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO

ALTER PROCEDURE [dbo].[bhp_NemoUsers]
WITH ENCRYPTION
AS
BEGIN

	DECLARE
		@Sql	NVARCHAR(4000)

	DECLARE
		@UserTab	TABLE 
						(
							UserId	INT, 
							Name	NVARCHAR(400)
						)

	INSERT INTO @UserTab VALUES (9999999, 'Administrator')

	IF OBJECT_ID('mp_Users', N'U') IS NOT NULL
		INSERT INTO @UserTab
			SELECT UserID, Name + ' (W)' FROM mp_Users WHERE IsDeleted = 0 AND IsLockedOut = 0

	IF OBJECT_ID('DOSTEP', N'U') IS NOT NULL AND OBJECT_ID('PRACOWNIK', N'U') IS NOT NULL
		INSERT INTO @UserTab
			SELECT 
				p.idx + 9000000, 
				imie + ' ' + nazwisko + ' (P)'
			FROM 
				PRACOWNIK p
				INNER JOIN DOSTEP d ON p.idx = d.idx 
			WHERE 
				p.del = 0

	SELECT * FROM @UserTab

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoTranslateSet] (
	@FieldId			INT,
	@Record			INT,
	@Language		NCHAR(5),
	@User				INT,
	@Translate		NVARCHAR(MAX)
)
WITH ENCRYPTION
AS
BEGIN

	SET XACT_ABORT ON;

	BEGIN TRAN

		IF EXISTS (
			SELECT 
				1
			FROM 
				[bht_NemoTranslates] t
			WHERE
				record_id				= @Record
				AND field_id		= @FieldId
				AND lang_code	= @Language
		)
			UPDATE
				[bht_NemoTranslates]
			SET
				translate				= @Translate,
				mtime					= GETDATE(),
				muser					= @User
			WHERE
				record_id				= @Record
				AND field_id		= @FieldId
				AND lang_code	= @Language
		ELSE
			INSERT INTO [bht_NemoTranslates] (record_id, field_id, translate, lang_code, muser)
					VALUES (@Record, @FieldId, @Translate, @Language, @User)
		
	COMMIT

	DECLARE
		@Sql				NVARCHAR(1000),
		@TabName		NVARCHAR(100)
		
	SELECT 
		@TabName = t2.name 
	FROM 
		n_Tree t1 
		INNER JOIN n_Tree t2 ON t1.parent = t2.id
	WHERE 
		t1.id = @FieldId
		
	SET @Sql = 'UPDATE ' + @TabName + ' SET mtime = GETDATE(), muser = ' + CAST(@User as NVARCHAR(100)) + ' WHERE idx = ' + CAST(@Record as NVARCHAR(100))
	EXEC sp_executesql @Sql

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoTranslateRemove] (
	@FieldId			INT,
	@Record			INT,
	@Language		NCHAR(5)
)
WITH ENCRYPTION
AS
BEGIN

	DELETE FROM 
		[bht_NemoTranslates] 
	WHERE
		record_id		= @Record
		AND field_id	= @FieldId
		AND lang_code	= @Language

	DECLARE
		@Sql				NVARCHAR(1000),
		@TabName		NVARCHAR(100)
		
	SELECT 
		@TabName = t2.name 
	FROM 
		n_Tree t1 
		INNER JOIN n_Tree t2 ON t1.parent = t2.id
	WHERE 
		t1.id = @FieldId
		
	SET @Sql = 'UPDATE ' + @TabName + ' SET mtime = GETDATE() WHERE idx = ' + CAST(@Record as NVARCHAR(100))
	EXEC sp_executesql @Sql

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoTranslateGetOryginal] (
	@GroupId	INT,
	@FieldId		INT,
	@RecordId	INT
)
WITH ENCRYPTION
AS
BEGIN
	
	DECLARE
		@Q				NVARCHAR(1000),
		@ColName	NVARCHAR(100),
		@Table		NVARCHAR(100)
		
	SELECT
		@ColName = name
	FROM
		n_Tree 
	WHERE
		id = @FieldId
		
	SELECT
		@Table = name
	FROM
		n_Tree 
	WHERE
		id = @GroupId
		
	SET @Q = 'SELECT ' + @ColName + ' FROM ' + @Table + ' WHERE idx = ' + CAST(@RecordId AS NVARCHAR(10))
	EXEC sp_executesql @Q

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoTranslateGetLanguages]
WITH ENCRYPTION
AS
BEGIN
	
	SELECT DISTINCT lang_code FROM [bht_NemoTranslates]

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoTranslateGet] (
	@GroupId	INT,
	@FieldId		INT,
	@RecordId	INT,
	@Language	NCHAR(5)
)
WITH ENCRYPTION
AS
BEGIN
	
	DECLARE
		@Val		NVARCHAR(MAX)
		
	SELECT @Val = dbo.[bhf_NemoTranslateGet](@FieldId,@RecordId,@Language,'')
	
	SELECT @Val

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoTablesLastModificationTimeGet]
WITH ENCRYPTION
AS
BEGIN
	DECLARE 
		@TabName		VARCHAR(200),
		@TabId			INT,
		@RelTabId		INT,
		@Type				VARCHAR(10),
		@Sql				VARCHAR(8000)
		
	SET XACT_ABORT ON
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
	BEGIN TRANSACTION

	DELETE FROM bht_NemoTableTimes_TempWork
	
	DECLARE ntables CURSOR LOCAL FAST_FORWARD FOR
		SELECT
			t.typ,
			t.id,
			name,
			t.rel
		FROM
			n_Tree t
		WHERE
			typ = 'fn' OR typ = 't'
	
	OPEN ntables
	
	WHILE 1 = 1
	BEGIN
	
		FETCH NEXT FROM ntables INTO @Type, @TabId, @TabName, @RelTabId
		IF(@@FETCH_STATUS <> 0) BREAK;
		
		IF @Type = 't'
		
		SET @Sql = 
			'INSERT INTO bht_NemoTableTimes_TempWork
			SELECT 
				' + CAST(@TabId as NVARCHAR(100)) + ' as TabId, MAX(mod_time) as LastModTime
			FROM
				(SELECT MAX(ctime) as mod_time FROM ' + @TabName + ' WHERE ctime IS NOT NULL
				UNION
				SELECT MAX(mtime) FROM ' + @TabName + ' WHERE mtime IS NOT NULL) x
			WHERE x.mod_time IS NOT NULL'
		
		
		EXEC(@Sql)
		
	END
	
	CLOSE ntables

	INSERT INTO bht_NemoTableTimes_TempWork
	SELECT 
		0, MAX([time])
	FROM
		(SELECT
			ctime as [time]
		FROM
			n_Tree
		WHERE
			ctime IS NOT NULL
		UNION
		SELECT
			mtime
		FROM
			n_Tree
		WHERE
			mtime IS NOT NULL) x

	DELETE FROM bht_NemoTableTimes

	INSERT INTO bht_NemoTableTimes
	SELECT 
		GroupId, MAX(LastTime)
	FROM 
		bht_NemoTableTimes_TempWork
	WHERE
		LastTime IS NOT NULL
	GROUP BY 
		GroupId
	
	COMMIT TRANSACTION
	
	SELECT * FROM bht_NemoTableTimes
END

GO

ALTER PROCEDURE [dbo].[bhp_NemoRecordIdentChange] (
	@Table		NVARCHAR(100),
	@RecordId	INT,
	@Ident			NVARCHAR(400),
	@UserId		INT
)
WITH ENCRYPTION
AS
BEGIN

	
	DECLARE @Q NVARCHAR(1000)
	
	SET @Q = 'UPDATE ' + @Table + ' SET identyfikator=''' + @Ident + ''', mtime=GETDATE(), muser=' + CAST(@UserId as NVARCHAR(10)) + ' WHERE idx=' + CAST(@RecordId as NVARCHAR(10))
	EXEC sp_executesql @Q
	
	
END

GO

ALTER PROCEDURE [dbo].[bhp_NemoRecordDelete] (
	@Table		NVARCHAR(100),
	@RecordId	INT,
	@UserId		INT
)
WITH ENCRYPTION
AS
BEGIN

	
	DECLARE @Q NVARCHAR(1000)
	
	SET @Q = 'UPDATE ' + @Table + ' SET del=1, mtime=GETDATE(), muser=' + CAST(@UserId as NVARCHAR(10)) + ' WHERE idx=' + CAST(@RecordId as NVARCHAR(10))
	EXEC sp_executesql @Q
	
	
END

GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupTabs] (
	@GroupId	INT
)
WITH ENCRYPTION
AS
BEGIN
	
	SELECT DISTINCT
		tabs.*
	FROM
		n_Tree t
		INNER JOIN bht_GroupColumnsSplit sc ON t.id = sc.ColumnId
		INNER JOIN bht_GroupTabs tabs ON sc.TabId = tabs.TabId
	WHERE
		parent = @GroupId
		AND (t.del IS NULL OR t.del = 0)

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupSystemNamesGet]
WITH ENCRYPTION
AS
BEGIN
	
	
	SELECT
		id as GroupId,
		name as SystemName
	FROM
		n_Tree
	WHERE
		(del IS NULL OR del = 0)
		AND typ IN ('T', 'V', 'TE')

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupSchemeGet] (
	@GroupId				INT
)
WITH ENCRYPTION
AS
BEGIN

	DECLARE
		@TableName		NVARCHAR(100),
		@IsExTable			BIT,
		@Parent				INT,
		@Sql					NVARCHAR(4000)

	SELECT
		@TableName = name,
		@IsExTable = CASE typ WHEN 'TE' THEN 1 ELSE 0 END,
		@Parent = parent
	FROM
		n_Tree
	WHERE
		id = @GroupId

	SET
		@Sql = 'SELECT TOP 1 * FROM ' + @TableName + ' t'

	IF @IsExTable = 1
	BEGIN
		SELECT @TableName = name FROM n_Tree WHERE id = @Parent
		SET @Sql = @Sql + ' INNER JOIN ' + @TableName + ' pt ON t.idx = pt.idx'
	END
	
	EXEC sp_executesql @Sql

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupGetAll]
WITH ENCRYPTION
AS
BEGIN

	SELECT 
		t.id as Id,
		g.name as Name,
		t.name as SystemName,
		g.Identyficator,
		t.name as SelectTable,
		CASE t.typ WHEN 'T' THEN 'Table' WHEN 'V' THEN 'View' WHEN 'TE' THEN 'ExtendedTable' ELSE '' END as [Type],
		ISNULL(g.EnableTranslate, 1) AS EnableTranslate,
		t.parent as ParentId
	FROM 
		n_Tree t
		LEFT JOIN bht_Groups g ON t.id = g.Id
	WHERE
		(del IS NULL OR del = 0)
		AND typ IN ('V', 'T', 'TE')
		AND (parent = 0 OR typ = 'TE')

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupGet] (
	@Id				INT
)
WITH ENCRYPTION
AS
BEGIN

	SELECT 
		t.id as Id,
		g.name as Name,
		t.name as SystemName,
		g.Identyficator,
		t.name as SelectTable,
		CASE t.typ WHEN 'T' THEN 'Table' WHEN 'V' THEN 'View' WHEN 'TE' THEN 'ExtendedTable' ELSE '' END as [Type],
		ISNULL(g.EnableTranslate, 1) AS EnableTranslate,
		t.parent as ParentId
	FROM 
		n_Tree t
		LEFT JOIN bht_Groups g ON t.id = g.Id
	WHERE
		(del IS NULL OR del = 0)
		AND typ IN ('V', 'T', 'TE')
		AND (parent = 0 OR typ = 'TE')
		AND t.id = @Id

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupExtensions]
WITH ENCRYPTION
AS
BEGIN

	SELECT
		*
	FROM
		bht_GroupExtensions
	
END

GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupExtensions]
WITH ENCRYPTION
AS
BEGIN

	SELECT
		*
	FROM
		bht_GroupExtensions
	
END

GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupColumnsSplit] (
	@GroupId	INT
)
WITH ENCRYPTION
AS
BEGIN
	
	SELECT 
		sc.*
	FROM
		n_Tree t
		INNER JOIN bht_GroupColumnsSplit sc ON t.id = sc.ColumnId
		INNER JOIN bht_GroupTabs tabs ON sc.TabId = tabs.TabId
	WHERE
		parent = @GroupId
		AND (t.del IS NULL OR t.del = 0)

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupColumnsGetVirtual] (
	@GroupId	INT
)
WITH ENCRYPTION
AS
BEGIN

	SELECT DISTINCT
		vc.*,
		STUFF((SELECT ',' + CAST(ColumnId as NVARCHAR(10)) FROM bht_ColumnsVirtualSet vcs WHERE vcs.VirtualId = vcs.VirtualId FOR XML PATH('')), 1, 1, '') as JoinedColumns
	FROM
		bht_ColumnsVirtual vc
		INNER JOIN bht_ColumnsVirtualSet vcs ON vc.Id = vcs.VirtualId
		INNER JOIN n_Tree t ON vcs.ColumnId = t.id
	WHERE
		t.parent = @GroupId

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupColumnsGet] (
	@GroupId				INT
)
WITH ENCRYPTION
AS
BEGIN
	SELECT 
		t.id as ColumnId,
		t.name as ColumnName,
		ct.EnumType as ColumnType,
		ISNULL(t.rel, 0) as RelatedGroupId,
		ISNULL(c.DisplayName, t.name) as DisplayName,
		ISNULL(c.FormattingValue, '{0}') as FormattingValue,
		ISNULL(c.Sort, 0) as ColumnSort,
		c.RegexValidation,
		c.[Description],
		ISNULL(c.HideForEdition, 0) as HideForEdition,
		c.CustomEditControl,
		t.parent as BelongsToGroupId,
		CASE tp.typ WHEN 'V' THEN 0 ELSE 1 END as Editable
	FROM
		n_Tree t
		INNER JOIN bht_ColumnTypes ct ON t.typ = ct.ColumnType
		LEFT JOIN bht_Columns c ON t.id = c.ColumnId
		INNER JOIN n_Tree tp ON t.parent = tp.id
	WHERE
		(t.parent = @GroupId OR t.parent = (SELECT TOP 1 parent FROM n_Tree WHERE id = @GroupId))
		AND (t.del IS NULL OR t.del = 0)

END

GO

ALTER PROCEDURE [dbo].[bhp_NemoColumnValidators] (
	@ColumnId	INT
)
WITH ENCRYPTION
AS
BEGIN

	SELECT
		cv.ColumnId,
		v.ValidatorId,
		v.InstanceClass,
		cv.XmlConfiguration
	FROM
		bht_ValidateColumnValidators v
		INNER JOIN bht_ValidateColumnValidatorConfig cv ON v.ValidatorId = cv.ValidatorId
	WHERE
		cv.ColumnId = @ColumnId	

END

GO

CREATE PROCEDURE bhp_Temp_DropIfExist
AS
	IF OBJECT_ID('bhf_NemoTranslateGetByPrefix', N'FN') IS NOT NULL
		DROP FUNCTION [bhf_NemoTranslateGetByPrefix]
GO
EXEC bhp_Temp_DropIfExist
GO
DROP PROCEDURE bhp_Temp_DropIfExist
GO

CREATE FUNCTION [dbo].[bhf_NemoTranslateGetByPrefix](
	@RecordIdx			INT,
	@Prefix					NVARCHAR(100),
	@Language			NCHAR(5),
	@DefaultValue	NVARCHAR(MAX) = ''
)
RETURNS NVARCHAR(MAX)
WITH ENCRYPTION
AS
BEGIN
	
	DECLARE
		@Group			NVARCHAR(50),
		@Column			NVARCHAR(50),
		@GroupId		INT,
		@ColumnId		INT
		
	SET @Group = SUBSTRING(@Prefix, 0, PATINDEX('%.%', @Prefix))
	SET @Column = SUBSTRING(@Prefix, PATINDEX('%.%', @Prefix) + 1, 50)
	
	SELECT @GroupId = id FROM n_Tree WHERE name = @Group AND parent = 0
	SELECT @ColumnId = id FROM n_Tree WHERE name = @Column AND parent = @GroupId
	
	RETURN (SELECT [dbo].[bhf_NemoTranslateGet] (
   @ColumnId,
   @RecordIdx,
   @Language,
  @DefaultValue))
	
END

GO

CREATE PROCEDURE bhp_Temp_DropIfExist
AS
	IF OBJECT_ID('bhf_NemoTranslateGetByName', N'FN') IS NOT NULL
		DROP FUNCTION [bhf_NemoTranslateGetByName]
GO
EXEC bhp_Temp_DropIfExist
GO
DROP PROCEDURE bhp_Temp_DropIfExist
GO

CREATE FUNCTION [dbo].[bhf_NemoTranslateGetByName](
	@ColumnName	VARCHAR(100),
	@TableId			INT,
	@Record			INT,
	@Language		NCHAR(5),
	@DefaultValue	NVARCHAR(MAX) = ''
)
RETURNS NVARCHAR(MAX)
WITH ENCRYPTION
AS
BEGIN

	RETURN
		COALESCE(
			(SELECT TOP 1
				translate
			FROM
				[bht_NemoTranslates]
			WHERE
				field_id		= (SELECT TOP 1 id FROM sTree WHERE name = @ColumnName AND parent = @TableId)
				AND record_id	= @Record
				AND lang_code	= @Language), @DefaultValue)

END

GO

ALTER FUNCTION [dbo].[bhf_NemoTranslateGet](
	@FieldId		INT,
	@Record			INT,
	@Language		NCHAR(5),
	@DefaultValue	NVARCHAR(MAX) = ''
)
RETURNS NVARCHAR(MAX)
WITH ENCRYPTION
AS
BEGIN

	RETURN
		COALESCE(
			(SELECT TOP 1
				translate
			FROM
				[bht_NemoTranslates]
			WHERE
				field_id		= @FieldId
				AND record_id	= @Record
				AND lang_code	= @Language), @DefaultValue)

END

GO

-- =============================================
-- Author:		Adrian Kaczmarek
-- Create date: 2013-08-05
-- Description:	Konwertuje date do int'a
-- =============================================
ALTER FUNCTION [dbo].[bhf_ConvertDateTo] 
(
	@Date datetime
)
RETURNS int
WITH ENCRYPTION
AS
BEGIN
	DECLARE @Result int

	SELECT @Result = DATEDIFF(d, '01-01-2001', @Date) + 73053

	RETURN @Result

END

GO

-- =============================================
-- Author:		Adrian Kaczmarek
-- Create date: 2013-08-05
-- Description:	Konwertuje date z czasem do int'a (unix)
-- =============================================
ALTER FUNCTION [dbo].[bhf_ConvertDateTimeTo] 
(
	@Date datetime
)
RETURNS bigint
WITH ENCRYPTION
AS
BEGIN
	DECLARE @Result bigint

	IF @Date >= '20380119' 
    BEGIN 
        SET @Result = CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119')) 
            + CONVERT(BIGINT, DATEDIFF(S, '20380119', @Date)) 
    END 
    ELSE 
        SET @Result = DATEDIFF(S, '19700101', @Date) 
    RETURN @Result 

END

GO

-- =============================================
-- Author:		Adrian Kaczmarek
-- Create date: 2013-08-05
-- Description:	Konwertuje wcześniej skonwertowaną date z czasem (int - unix) do datetime
-- =============================================
ALTER FUNCTION [dbo].[bhf_ConvertDateTimeFrom] 
(
	@UnixTimestamp bigint
)
RETURNS datetime
WITH ENCRYPTION
AS
BEGIN
	DECLARE @Result datetime

	SELECT @Result = DATEADD(second, @UnixTimestamp,{d '1970-01-01'});

	RETURN @Result

END


GO

-- =============================================
-- Author:		Adrian Kaczmarek
-- Create date: 2013-08-05
-- Description:	Konwertuje wcześniej skonwertowaną date (int) do datetime
-- =============================================
ALTER FUNCTION [dbo].[bhf_ConvertDateFrom] 
(
	@DateConverted int
)
RETURNS datetime
WITH ENCRYPTION
AS
BEGIN
	DECLARE @Result datetime

	SELECT @Result = DATEADD(d, @DateConverted - 73053, '01-01-2001')

	RETURN @Result

END

